from common_func_defs import *

def dy_flagship_store_living_room_day_stat_exe(living_start_time, livestreamer_douyin_id,
                                               living_count, good_series, living_price, living_incl_tax_service_cost,
                                               living_commission_rate, other_cost, first_level_cost_support,
                                               sixty_nine_code):

    global df_69_info

    ## 测试用
    # livestreamer_douyin_id = '13.henbang'
    # living_count = 0.1
    # living_price = 1
    # living_incl_tax_service_cost = 0.1
    # living_commission_rate = 0.1
    # other_cost = 0.3
    # first_level_cost_support = 0.2
    # good_series = '樱肌感'
    # sixty_nine_code = 69214434986431
    # living_start_time = '2023-08-01'

    if living_price == '':
        raise ValueError('直播价格不可为空')
        pass
    
    try:
        living_count = float(living_count)
        living_price = float(living_price)
        living_incl_tax_service_cost = float(living_incl_tax_service_cost)
        living_commission_rate = float(living_commission_rate)
        other_cost = float(other_cost)
        other_cost = float(other_cost)
    except:
        raise ValueError('请检查数值类输入变量是否正确输入')

    # 将字符串时间转换为时间格式
    time_format = "%Y-%m-%d"
    time = datetime.strptime(living_start_time, time_format)

    # 提取月份
    target_month = time.strftime('%m')

    # 提取季度
    target_quarter = (time.month - 1) // 3 + 1

    # livestreamer_name, livestreamer_douyin_id, living_start_time, living_max_online_user_count, living_all_goods_view_to_trans_users_rate, sixty_nine_code
    # 月
    query_all_years_month = f"""SELECT * FROM dy_flagship_store_living_room_day WHERE MONTH(stat_time) = {target_month}"""
    # 季度
    query_all_years_quarter = f"SELECT * FROM dy_flagship_store_living_room_day WHERE EXTRACT(QUARTER FROM stat_time) = {target_quarter}"
    # 全
    query_all = f"""SELECT * FROM dy_flagship_store_living_room_day"""

    queries = [query_all_years_month, query_all_years_quarter, query_all]

    global engine
    engine = create_engine(DB_CONNECT)

    for query in queries:
        # 执行 SQL 查询并将结果存储到 DataFrame 中
        df_dy_flagship_store_living_room_day = pd.read_sql_query(query, engine)

        df_69_info = pd.read_sql_query('SELECT * FROM good_indicator', engine)
        df_69_info.drop(['id', 'create_time'], axis=1, inplace=True)

        tp = df_dy_flagship_store_living_room_day.merge(df_69_info, on='sixty_nine_code', how='left')

        # 筛选对应系列和对应达人
        tp = tp[tp['good_series'] == good_series]  # 按照系列匹配
        tp = tp[tp['livestreamer_douyin_id'] == livestreamer_douyin_id]

        if not tp.empty:
            break
            pass
        pass

    if tp.empty:
        return '输入的达人就没有历史数据'  # 全都没有匹配上

    # livestreamer_name
    output = tp.groupby(['livestreamer_douyin_id'])['livestreamer_name'].agg(
        lambda x: ';'.join(x.unique())).reset_index(
        name='livestreamer_name')

    df_69_info = df_69_info[['sixty_nine_code', 'good_product_combination', 'good_giveaway_item_price',
                             'good_initial_price', 'good_giveaway_item_price_ratio']]
    # sixty_nine_code
    output['sixty_nine_code'] = str(sixty_nine_code)

    output = output.merge(df_69_info, how='left', on='sixty_nine_code')  # 补充69码相关信息

    output['living_start_time'] = living_start_time
    output['living_count'] = living_count
    output['good_series'] = good_series
    output['living_price'] = living_price
    output['living_incl_tax_service_cost'] = living_incl_tax_service_cost
    output['living_commission_rate'] = living_commission_rate
    output['other_cost'] = other_cost
    output['first_level_cost_support'] = first_level_cost_support

    #################################### 使用的 ################################################
    # predict_living_max_online_user_count
    df_ttp = tp.groupby(['livestreamer_douyin_id'])['living_max_online_user_count'].agg(
        lambda x: int(x.median())).reset_index(name='predict_living_max_online_user_count')

    output = output.merge(df_ttp, on=['livestreamer_douyin_id'], how='left')
    del df_ttp

    # predict_conversion_rate
    df_ttp = tp.groupby(['livestreamer_douyin_id'])['living_all_goods_view_to_trans_users_rate'].agg(
        lambda x: x.median()).reset_index(name='predict_conversion_rate')

    output = output.merge(df_ttp, on=['livestreamer_douyin_id'], how='left')
    del df_ttp

    # good_trans_count
    output['good_trans_count'] = output['predict_living_max_online_user_count'] * output['predict_conversion_rate'] * living_count

    # actual_gmv
    output['actual_gmv'] = output['predict_living_max_online_user_count'] * output['predict_conversion_rate'] * living_count * living_price

    # initial_sales_gmv	开单销售额计算	计算：=最高峰值预测×转化率预测×直播次数×开单价格
    output['initial_sales_gmv'] = output['predict_living_max_online_user_count'] * output['predict_conversion_rate'] * living_count * output['good_initial_price']

    # discount_rate	折扣费率	计算：=（开单价格-直播价格）/开单价格
    # [就是把价格指标计算处的实际到手价用直播价替换]
    output['discount_rate'] = (output['good_initial_price'] - living_price) / output['good_initial_price']

    # discount_fee	折扣费用	计算：=折扣费率*开单销售额
    output['discount_fee'] = output['discount_rate'] * output['initial_sales_gmv']

    # giveaway_item_amount	赠品金额	计算：=赠品费率*开单销售额
    output['giveaway_item_amount'] = output['good_giveaway_item_price_ratio'] / output['initial_sales_gmv']

    # commission_fee	佣金费额	计算：=(佣金率*实收销售额)/开单销售额
    output['commission_fee'] = output['living_commission_rate'] * output['actual_gmv'] / output['initial_sales_gmv']

    # platform_discount_fee	平台扣点费用	计算：=实收销售额*2.5%（因为抖音和淘宝都是2.5%）
    output['platform_discount_fee'] = 0.025 * output['actual_gmv']

    # total_cost	费额合计	计算：=服务费+佣金费额+
    # 平台扣点费用+折扣费用+
    # 赠品金额+其余费用
    output['total_cost'] = output['living_incl_tax_service_cost'] + output['commission_fee'] + \
                           output['platform_discount_fee'] + output['discount_fee'] + \
                           output['giveaway_item_amount'] + output['other_cost']

    # operating_cost	运营费额	计算：=费额合计-一级费用支持
    output['operating_cost'] = output['total_cost'] - output['first_level_cost_support']

    # total_rate_initial	总费率（base开单销售额）	计算：=总费额合计/开单销售额
    output['total_rate_initial'] = output['total_cost'] / output['initial_sales_gmv']

    # total_rate	总费率	计算：=总费额合计/实收销售额预估
    output['total_rate'] = output['total_cost'] / output['actual_gmv']

    # total_roi	ROI	计算：=1/总费率预估
    output['total_roi'] = 1 / output['total_rate']

    # 加工报表表字段英文名→中文名
    def predict_corr_entozh_res(upload_df, table_zh_name):
        base_name_df = pd.read_excel('C:/Users/简皓/Desktop/丝宝项目最后订正/预测输出数据字典_0420更新.xlsx', table_zh_name, header=1)  # 前端修改位置，表在腾讯文档

        # 创建从中文名到英文名的映射字典
        column_name_map = base_name_df.set_index('字段名')['释义'].to_dict()
        column_name_map["row_count"] = "数据记录数量"
        # 使用映射字典重命名upload_df的列名，中文名（释义）→英文名（字段名）
        for old_col, new_col in column_name_map.items():
            if old_col in upload_df.columns:
                upload_df.rename(columns={old_col: new_col}, inplace=True)
        return upload_df


    # 转中文字段名
    output = predict_corr_entozh_res(output, '抖音达播预测结果表新')

    return output